package com.davidhigenbottam.HeatMap.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;

import com.davidhigenbottam.HeatMap.shared.Institution;
import com.davidhigenbottam.HeatMap.shared.Province;
import com.davidhigenbottam.HeatMap.shared.Query;


public class StaffWithPhdProvinceData extends ProvinceData {

	public StaffWithPhdProvinceData(Query query) {
		super(query);
	}

	@Override
	public String getDescription() {
		return "Staff with PhD";
	}


	@Override
	public void getProvincesFromDatabase() {
		Connection c = null;
		try {
			c = getConnection();
			getAllProvinces(c);
						
			String statement = "select province.id,province.name,prov_name, sum(total) as sumtotal  "
					+ "from phd_staff_group, province "
					+ "where province.student_group_id = phd_staff_group.prov_id "
					+ "and province.country_id=? "
					+ "group by province.id";
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);
			
			stmt.setString(1, query.getCountryCode());

			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					String prov = rs.getString("name");
					Long sumTotal = rs.getLong("sumtotal");
					Province p = provinces.get(prov);
					p.setVal(Double.valueOf(sumTotal.doubleValue()));
					//log.info("prov:"+prov+" sumTotal:"+sumTotal);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
		
	}

	@Override
	public void getInstitutionsFromDatabase(Province p) {
		Connection c = null;
		try {
			c = getConnection();
	
			
			String statement = "select inst_id as id, inst_name as name, sum(total) as sumtotal "
					+ "from phd_staff_group, province "
					+ "where province.student_group_id = phd_staff_group.prov_id "
					+ "and province.id=? "
					+ "group by inst_id";
			log.info("statement:"+statement);
			
			PreparedStatement stmt = c.prepareStatement(statement);

			stmt.setLong(1, p.getId());
//			if(filterPosition)
//				stmt.setString(idx++, query.getStaffPosition());
			
			
			if (stmt.execute()) {
				ResultSet rs = stmt.getResultSet();
				while (rs.next()) {
					//Long id = rs.getLong("id");
					String inst = rs.getString("name");
					
					Long sumTotal = rs.getLong("sumtotal");
					
					Institution i= new Institution(inst,sumTotal);
					p.addInstitution(i);
				}
			}
		} catch (SQLException e) {
			log.log(Level.SEVERE, "SQLException", e);
			throw new RuntimeException(e);
		} finally {
			if (c != null)
				try {
					c.close();
				} catch (SQLException ignore) {
				}
		}
				
		
	}

	

}
